from flask import Blueprint, render_template, request, send_file, flash, redirect, url_for
from flask_login import login_required, current_user
from app.models import Sample, MarkerAnalysis
import pandas as pd
import matplotlib
matplotlib.use('Agg')  # 设置为非交互式后端
import matplotlib.pyplot as plt
import seaborn as sns
from io import BytesIO
import os
from datetime import datetime
from openpyxl import load_workbook
from openpyxl.drawing.image import Image

bp = Blueprint('reports', __name__, url_prefix='/reports')

@bp.route('/')
@login_required
def index():
    # 导入模型
    from app.models import Sample, MarkerAnalysis
    
    # 获取数据的日期范围
    first_sample = Sample.query.filter_by(user_id=current_user.id).order_by(Sample.collection_date.asc()).first()
    last_sample = Sample.query.filter_by(user_id=current_user.id).order_by(Sample.collection_date.desc()).first()
    
    date_range = {
        'start': first_sample.collection_date.strftime('%Y-%m-%d') if first_sample else None,
        'end': last_sample.collection_date.strftime('%Y-%m-%d') if last_sample else None
    }
    
    return render_template('reports/index.html', date_range=date_range)

def check_data_availability(start_date, end_date):
    """检查指定日期范围内是否有数据"""
    samples_count = Sample.query.filter(
        Sample.user_id == current_user.id,
        Sample.collection_date >= start_date.date(),
        Sample.collection_date <= end_date.date()
    ).count()
    
    analyses_count = MarkerAnalysis.query.join(Sample).filter(
        MarkerAnalysis.user_id == current_user.id,
        Sample.collection_date >= start_date.date(),
        Sample.collection_date <= end_date.date()
    ).count()
    
    return {
        'samples': samples_count,
        'analyses': analyses_count
    }

@bp.route('/generate', methods=['POST'])
@login_required
def generate():
    report_type = request.form.get('report_type')
    start_date = request.form.get('start_date')
    end_date = request.form.get('end_date')
    
    if not all([report_type, start_date, end_date]):
        flash('请填写所有必要字段', 'danger')
        return redirect(url_for('reports.index'))
    
    try:
        # 转换日期字符串为datetime对象
        start_date = datetime.strptime(start_date, '%Y-%m-%d')
        end_date = datetime.strptime(end_date, '%Y-%m-%d')
        
        # 检查数据可用性
        data_counts = check_data_availability(start_date, end_date)
        if data_counts['samples'] == 0 and data_counts['analyses'] == 0:
            flash(f'所选时间范围 ({start_date.date()} 至 {end_date.date()}) 内没有数据', 'warning')
            return redirect(url_for('reports.index'))
        
        if report_type == 'hardness':
            if data_counts['samples'] == 0:
                flash('所选时间范围内没有硬度分析数据', 'warning')
                return redirect(url_for('reports.index'))
            return generate_hardness_report(start_date, end_date)
        elif report_type == 'marker':
            if data_counts['analyses'] == 0:
                flash('所选时间范围内没有分子标记分析数据', 'warning')
                return redirect(url_for('reports.index'))
            return generate_marker_report(start_date, end_date)
        else:
            flash('不支持的报告类型', 'danger')
            return redirect(url_for('reports.index'))
            
    except Exception as e:
        flash(f'生成报告失败：{str(e)}', 'danger')
        return redirect(url_for('reports.index'))

def generate_hardness_report(start_date, end_date):
    """生成硬度分析报告"""
    try:
        # 调整查询条件，确保包含整个日期范围
        samples = Sample.query.filter(
            Sample.user_id == current_user.id,
            Sample.collection_date >= start_date.date(),  # 使用 date() 只比较日期部分
            Sample.collection_date <= end_date.date()     # 使用 <= 包含结束日期
        ).all()
        
        if not samples:
            flash('所选时间范围内没有数据', 'warning')
            return redirect(url_for('reports.index'))
        
        # 创建数据框
        data = {
            '样品编号': [s.sample_id for s in samples],
            '品种名称': [s.variety_name for s in samples],
            '采集日期': [s.collection_date for s in samples],
            '硬度值': [s.hardness for s in samples],
            '蛋白质含量': [s.protein_content for s in samples],
            '水分含量': [s.moisture_content for s in samples]
        }
        df = pd.DataFrame(data)
        
        # 生成统计图表
        plt.figure(figsize=(10, 6))
        sns.boxplot(data=df[['硬度值', '蛋白质含量', '水分含量']])
        plt.title('样品特性分布')
        
        # 保存图表
        img_buffer = BytesIO()
        plt.savefig(img_buffer, format='png', bbox_inches='tight', dpi=300)
        img_buffer.seek(0)  # 重要：将缓冲区指针移回开始
        plt.close('all')  # 关闭所有图表
        
        # 生成Excel报告
        excel_buffer = BytesIO()
        with pd.ExcelWriter(excel_buffer, engine='openpyxl') as writer:
            df.to_excel(writer, sheet_name='数据汇总', index=False)
            
            # 添加描述性统计
            stats = df[['硬度值', '蛋白质含量', '水分含量']].describe()
            stats.to_excel(writer, sheet_name='统计分析')
        
        excel_buffer.seek(0)
        
        # 将图表插入Excel
        wb = load_workbook(excel_buffer)
        ws = wb.create_sheet('图表分析')
        img = Image(img_buffer)
        ws.add_image(img, 'A1')
        
        # 保存最终报告
        final_buffer = BytesIO()
        wb.save(final_buffer)
        final_buffer.seek(0)
        
        return send_file(
            final_buffer,
            mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
            as_attachment=True,
            download_name=f'硬度分析报告_{datetime.now().strftime("%Y%m%d")}.xlsx'
        )
    except Exception as e:
        flash(f'生成报告失败：{str(e)}', 'danger')
        return redirect(url_for('reports.index'))

def generate_marker_report(start_date, end_date):
    """生成分子标记分析报告"""
    try:
        # 调整查询条件
        analyses = MarkerAnalysis.query.join(Sample).filter(
            MarkerAnalysis.user_id == current_user.id,
            Sample.collection_date >= start_date.date(),
            Sample.collection_date <= end_date.date()
        ).all()
        
        if not analyses:
            flash('所选时间范围内没有数据', 'warning')
            return redirect(url_for('reports.index'))
        
        # 创建数据框
        data = {
            '样品编号': [a.sample.sample_id for a in analyses],
            '品种名称': [a.sample.variety_name for a in analyses],
            'Pina基因型': [a.pina_type for a in analyses],
            'Pinb基因型': [a.pinb_type for a in analyses],
            '籽粒硬度': [a.grain_hardness for a in analyses],
            '分析日期': [a.analysis_date for a in analyses]
        }
        df = pd.DataFrame(data)
        
        # 生成基因型分布图
        plt.figure(figsize=(12, 5))
        
        plt.subplot(1, 2, 1)
        pina_counts = df['Pina基因型'].value_counts()
        plt.pie(pina_counts, labels=pina_counts.index, autopct='%1.1f%%')
        plt.title('Pina基因型分布')
        
        plt.subplot(1, 2, 2)
        pinb_counts = df['Pinb基因型'].value_counts()
        plt.pie(pinb_counts, labels=pinb_counts.index, autopct='%1.1f%%')
        plt.title('Pinb基因型分布')
        
        # 保存图表
        img_buffer = BytesIO()
        plt.savefig(img_buffer, format='png', bbox_inches='tight', dpi=300)
        img_buffer.seek(0)  # 重要：将缓冲区指针移回开始
        plt.close('all')  # 关闭所有图表
        
        # 生成Excel报告
        excel_buffer = BytesIO()
        with pd.ExcelWriter(excel_buffer, engine='openpyxl') as writer:
            df.to_excel(writer, sheet_name='数据汇总', index=False)
            
            # 添加基因型统计
            gene_stats = pd.DataFrame({
                'Pina基因型分布': pina_counts,
                'Pinb基因型分布': pinb_counts
            })
            gene_stats.to_excel(writer, sheet_name='基因型统计')
        
        excel_buffer.seek(0)
        
        # 将图表插入Excel
        wb = load_workbook(excel_buffer)
        ws = wb.create_sheet('图表分析')
        img = Image(img_buffer)
        ws.add_image(img, 'A1')
        
        # 保存最终报告
        final_buffer = BytesIO()
        wb.save(final_buffer)
        final_buffer.seek(0)
        
        return send_file(
            final_buffer,
            mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
            as_attachment=True,
            download_name=f'分子标记分析报告_{datetime.now().strftime("%Y%m%d")}.xlsx'
        )
    except Exception as e:
        flash(f'生成报告失败：{str(e)}', 'danger')
        return redirect(url_for('reports.index'))
